﻿/*
	dbo.SpStoreDefaultParameterForUser Procedure created by Jens Suessmeyer (12/09/2009)

	Procedure to get the last recently used parameter values in a report for a certain user
	and a specific server and a specific report
	
	Input Parameters:	@UserId		= UserId passed in by the Reportexecution call (should be hashed for security reasons
						@ParameterValues	= A delimited list of values to store
						@ReportName	= Report Name which called the proceedure
						@ReportServerUrl	= Report Server Url where the Report was called from
	Output			:	Nothing
*/


CREATE PROCEDURE [dbo].[SpStoreDefaultParameterForUser]
(
	@ParameterValues VARCHAR(MAX),
	@UserId VARCHAR(50),
	@ReportServerUrl VARCHAR(128),
	@ReportName VARCHAR(128)
)
AS

DECLARE @RowCount INT
DECLARE @ExecutionId INT
DECLARE @ParameterStack VARCHAR(MAX)
DECLARE @ParameterName VARCHAR(MAX)


IF (SELECT TOP(1) ConfigValue FROM dbo.Configuration WHERE ConfigType = 'HashUserId') = 1
	SET @UserId = HashBytes('SHA1', @UserId)
	
INSERT INTO dbo.DefaultReportParameterExecution
(
	UserId,
	ReportServerUrl,
	ReportName
)
SELECT 
	@UserId,
	@ReportServerUrl,
	@ReportName
	
SET @ExecutionId = SCOPE_IDENTITY()
	
SELECT OccurenceId, SplitValue
INTO #Parameters
FROM dbo.Split(@ParameterValues,';' )
s
SET @RowCount = @@ROWCOUNT

WHILE @RowCount > 0
BEGIN

	SELECT @ParameterStack = SplitValue FROM #Parameters WHERE OccurenceId = @RowCount
		
	SELECT SplitValue FROM #Parameters WHERE OccurenceId = @RowCount
	
	SELECT @ParameterName = SplitValue FROM dbo.Split(@ParameterStack, '^') Where OccurenceId = 1
	SELECT @ParameterValues = CASE WHEN SplitValue = '<NULL>' THEN NULL ELSE SplitValue END FROM dbo.Split(@ParameterStack, '^') Where OccurenceId = 2

PRINT @ParameterName + @ParameterValues

	IF @ParameterValues IS NULL
		INSERT INTO dbo.DefaultReportParameters
		(
			ExecutionId,
			ParameterName,
			ParameterValue
		)
		Select 
			@ExecutionId,
			@ParameterName,
			NULL
		
	ELSE
	INSERT INTO dbo.DefaultReportParameters
	(
		ExecutionId,
		ParameterName,
		ParameterValue
	)
	Select 
		@ExecutionId,
		@ParameterName,
		SplitValue
	FROM 
	dbo.Split(@ParameterValues, '|')
	
	SET @RowCount = @RowCount -1 

END
